package com.car.util;

import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.car.dal.dao.moneys.TgMoneyDao;
import com.car.dal.dao.office.TgOfficeDao;
import com.car.dal.dao.reception.TgReceptionDao;
import com.car.dal.dao.vote.JsVoteDao;
import com.car.dal.object.*;
import com.car.service.money.MoneyService;
import com.car.web.controller.base.BaseVo;

import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONArray;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.json.JSONObject;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class WriterExcel {

    public static String writeExcelReserveByTime(List<TgReserve> reserveList, String type) {
        //标题行
        //key	client	预约日期	预约时间	客户名称	省份	城市	客户来源	类型	非测评备注	预约人	预约接待人	状态	测评中心	实际接待人	"
        //	学员姓名"	测评老师	是否报名	训练类型	课时数	会员卡	仪器编号	过期日期	报价情况	金额变动	"
        //	学员姓名2"	测评老师2	是否报名2	训练类型2	课时数2	会员卡2	仪器编号2	过期日期2	报价情况2	金额变动2
        //<h3><span>选择课程类型：</span>${list.course_type}</h3>
        String title[] = {"登记日期", "预约日期", "预约时间", "客户名称", "省份", "城市", "客户来源", "类型", "非测评备注", "预约人 - 推广人", "电话", "QQ", "预约接待人", "状态", "测评中心", "实际接待人",
                "学员姓名", "测评老师", "是否报名", "训练类型", "课时数", "会员卡", "仪器编号", "过期日期", "报价情况", "金额变动",
                "学员姓名2", "测评老师2", "是否报名2", "训练类型2", "课时数2", "会员卡2", "仪器编号2", "过期日期2", "报价情况2", "金额变动2"};

        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            if (type.equals("1")) {
                name = "客户预约统计表(登记日期).xls";
            } else if (type.equals("2")) {
                name = "客户预约统计表(预约日期).xls";
            } else {
                name = "客户预约统计表1.xls";
            }
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("学员单次训练记录", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);

            //写入内容（学员信息）
            for (int i = 0; i < title.length; i++) {  //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < reserveList.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, reserveList.get(i).getStartTime()));
                if (reserveList.get(i).getDate() != null && !"".equals(reserveList.get(i).getDate())) {
                    sheet.addCell(new Label(1, i + 1, sdf.format(reserveList.get(i).getDate())));
                } else {
                    sheet.addCell(new Label(1, i + 1, ""));
                }
                sheet.addCell(new Label(2, i + 1, reserveList.get(i).getTime()));
                sheet.addCell(new Label(3, i + 1, reserveList.get(i).getNickname()));
                sheet.addCell(new Label(4, i + 1, reserveList.get(i).getProvince()));
                sheet.addCell(new Label(5, i + 1, reserveList.get(i).getCity()));
                sheet.addCell(new Label(6, i + 1, reserveList.get(i).getSourcens()));
                if (reserveList.get(i).getNon() == 0) {
                    sheet.addCell(new Label(7, i + 1, "测评"));
                } else {
                    sheet.addCell(new Label(7, i + 1, "非测评"));
                }
                sheet.addCell(new Label(8, i + 1, reserveList.get(i).getRemark()));
                sheet.addCell(new Label(9, i + 1, reserveList.get(i).getSigner()+" - "+reserveList.get(i).getTuiNickName()));
                sheet.addCell(new Label(10, i + 1, reserveList.get(i).getPhone()));
                sheet.addCell(new Label(11, i + 1, reserveList.get(i).getQq()));
                sheet.addCell(new Label(12, i + 1, reserveList.get(i).getReceptioner()));
                if (reserveList.get(i).getState() == 1) {
                    sheet.addCell(new Label(13, i + 1, "待确认"));
                } else if (reserveList.get(i).getState() == 2) {
                    sheet.addCell(new Label(13, i + 1, "已出现"));
                } else if (reserveList.get(i).getState() == 3) {
                    sheet.addCell(new Label(13, i + 1, "未出现"));
                }
                sheet.addCell(new Label(14, i + 1, reserveList.get(i).getRegionns()));
                sheet.addCell(new Label(15, i + 1, reserveList.get(i).getReceuser()));

                if (reserveList.get(i).getSmlist() != null && reserveList.get(i).getSmlist().size() > 0) {
                    sheet.addCell(new Label(16, i + 1, reserveList.get(i).getSmlist().get(0).getKeyname()));
                    sheet.addCell(new Label(17, i + 1, reserveList.get(i).getSmlist().get(0).getTestuser()));
                    if (reserveList.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(18, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(18, i + 1, "否"));
                    }
                    if (reserveList.get(i).getSmlist().get(0).getType() != null && !"".equals(reserveList.get(i).getSmlist().get(0).getType())) {
                        if (reserveList.get(i).getSmlist().get(0).getType().equals("a")) {
                            sheet.addCell(new Label(19, i + 1, "中心训练"));
                        } else if (reserveList.get(i).getSmlist().get(0).getType().equals("b")) {
                            sheet.addCell(new Label(19, i + 1, "家庭训练"));
                        } else if (reserveList.get(i).getSmlist().get(0).getType().contains("ab") || reserveList.get(i).getSmlist().get(0).getType().contains("ac")) {
                            sheet.addCell(new Label(19, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(19, i + 1, ""));
                    }
                    if (reserveList.get(i).getSmlist().get(0).getLesson() != null && !"".equals(reserveList.get(i).getSmlist().get(0).getLesson() != null)) {
                        sheet.addCell(new Label(20, i + 1, String.valueOf(reserveList.get(i).getSmlist().get(0).getLesson())));
                    } else {
                        sheet.addCell(new Label(20, i + 1, ""));
                    }
                    sheet.addCell(new Label(21, i + 1, reserveList.get(i).getSmlist().get(0).getCard()));
                    sheet.addCell(new Label(22, i + 1, reserveList.get(i).getSmlist().get(0).getSerial()));
                    sheet.addCell(new Label(23, i + 1, reserveList.get(i).getSmlist().get(0).getDeadline()));
                    sheet.addCell(new Label(24, i + 1, reserveList.get(i).getSmlist().get(0).getPrice()));
                    sheet.addCell(new Label(25, i + 1, reserveList.get(i).getSmlist().get(0).getMoneylog()));
                }
                if (reserveList.get(i).getSmlist() != null && reserveList.get(i).getSmlist().size() > 1) {
                    sheet.addCell(new Label(16, i + 1, reserveList.get(i).getSmlist().get(0).getKeyname()));
                    sheet.addCell(new Label(17, i + 1, reserveList.get(i).getSmlist().get(0).getTestuser()));
                    if (reserveList.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(18, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(18, i + 1, "否"));
                    }
                    if (reserveList.get(i).getSmlist().get(0).getType() != null && !"".equals(reserveList.get(i).getSmlist().get(0).getType())) {
                        if (reserveList.get(i).getSmlist().get(0).getType().equals("a")) {
                            sheet.addCell(new Label(19, i + 1, "中心训练"));
                        } else if (reserveList.get(i).getSmlist().get(0).getType().equals("b")) {
                            sheet.addCell(new Label(19, i + 1, "家庭训练"));
                        } else if (reserveList.get(i).getSmlist().get(0).getType().contains("ab") || reserveList.get(i).getSmlist().get(0).getType().contains("ac")) {
                            sheet.addCell(new Label(19, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(19, i + 1, ""));
                    }
                    if (reserveList.get(i).getSmlist().get(0).getLesson() != null && !"".equals(reserveList.get(i).getSmlist().get(0).getLesson() != null)) {
                        sheet.addCell(new Label(20, i + 1, String.valueOf(reserveList.get(i).getSmlist().get(0).getLesson())));
                    } else {
                        sheet.addCell(new Label(20, i + 1, ""));
                    }
                    sheet.addCell(new Label(21, i + 1, reserveList.get(i).getSmlist().get(0).getCard()));
                    sheet.addCell(new Label(22, i + 1, reserveList.get(i).getSmlist().get(0).getSerial()));
                    sheet.addCell(new Label(23, i + 1, reserveList.get(i).getSmlist().get(0).getDeadline()));
                    sheet.addCell(new Label(24, i + 1, reserveList.get(i).getSmlist().get(0).getPrice()));
                    sheet.addCell(new Label(25, i + 1, reserveList.get(i).getSmlist().get(0).getMoneylog()));

                    sheet.addCell(new Label(26, i + 1, reserveList.get(i).getSmlist().get(1).getKeyname()));
                    sheet.addCell(new Label(27, i + 1, reserveList.get(i).getSmlist().get(1).getTestuser()));
                    if (reserveList.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(28, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(28, i + 1, "否"));
                    }
                    if (reserveList.get(i).getSmlist().get(1).getType() != null && !"".equals(reserveList.get(i).getSmlist().get(1).getType())) {
                        if (reserveList.get(i).getSmlist().get(1).getType().equals("a")) {
                            sheet.addCell(new Label(29, i + 1, "中心训练"));
                        } else if (reserveList.get(i).getSmlist().get(1).getType().equals("b")) {
                            sheet.addCell(new Label(29, i + 1, "家庭训练"));
                        } else if (reserveList.get(i).getSmlist().get(1).getType().contains("ab") || reserveList.get(i).getSmlist().get(1).getType().contains("ac")) {
                            sheet.addCell(new Label(29, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(29, i + 1, ""));
                    }
                    if (reserveList.get(i).getSmlist().get(1).getLesson() != null && !"".equals(reserveList.get(i).getSmlist().get(1).getLesson() != null)) {
                        sheet.addCell(new Label(30, i + 1, String.valueOf(reserveList.get(i).getSmlist().get(1).getLesson())));
                    } else {
                        sheet.addCell(new Label(30, i + 1, ""));
                    }
                    sheet.addCell(new Label(31, i + 1, reserveList.get(i).getSmlist().get(1).getCard()));
                    sheet.addCell(new Label(32, i + 1, reserveList.get(i).getSmlist().get(1).getSerial()));
                    sheet.addCell(new Label(33, i + 1, reserveList.get(i).getSmlist().get(1).getDeadline()));
                    sheet.addCell(new Label(34, i + 1, reserveList.get(i).getSmlist().get(1).getPrice()));
                    sheet.addCell(new Label(35, i + 1, reserveList.get(i).getSmlist().get(1).getMoneylog()));
                }
            }

            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
        }
        return name;
    }

    public static String writeExcelReceptionByTime(List<TgReception> recelist, String type,String sedate) {
        String title[] = {"接待日期", "客户名称", "省份", "城市", "客户来源", "预约人", "电话", "QQ", "接待人", "测评中心", "学员姓名", "测评老师", "是否报名", "训练类型", "课时数", "会员卡", "仪器编号", "过期日期", "报价情况", "金额变动",
                "学员姓名2", "测评老师2", "是否报名2", "训练类型2", "课时数2", "会员卡2", "仪器编号2", "过期日期2", "报价情况2", "金额变动2"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            if (type.equals("1")) {
                name = "客户接待统计表("+sedate+").xls";
            } else {
                name = "客户接待统计表.xls";
            }
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
        //  WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("接待记录", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);

            //写入内容（学员信息）
            for (int i = 0; i < title.length; i++) {  //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < recelist.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, recelist.get(i).getGtime()));
                sheet.addCell(new Label(1, i + 1, recelist.get(i).getNickname()));
                sheet.addCell(new Label(2, i + 1, recelist.get(i).getProvince()));
                sheet.addCell(new Label(3, i + 1, recelist.get(i).getCity()));
                sheet.addCell(new Label(4, i + 1, recelist.get(i).getSourcens()));
                sheet.addCell(new Label(5, i + 1, recelist.get(i).getReceuser()));
                if (recelist.get(i).getPhone() != null && recelist.get(i).getPhone() != "") {
                    if (recelist.get(i).getPhone().contains("[]")) {
                        sheet.addCell(new Label(6, i + 1, ""));
                    } else {
                        sheet.addCell(new Label(6, i + 1, recelist.get(i).getPhone()));
                    }
                }
                sheet.addCell(new Label(7, i + 1, recelist.get(i).getQq()));
                sheet.addCell(new Label(8, i + 1, recelist.get(i).getReceptionns()));
                sheet.addCell(new Label(9, i + 1, recelist.get(i).getRegionns()));

                if (recelist.get(i).getSmlist() != null && recelist.get(i).getSmlist().size() > 0) {
                    sheet.addCell(new Label(10, i + 1, recelist.get(i).getSmlist().get(0).getKeyname()));
                    if (recelist.get(i).getSmlist().get(0).getTestuser() != null && recelist.get(i).getSmlist().get(0).getTestuser() != "") {
                        sheet.addCell(new Label(11, i + 1, recelist.get(i).getSmlist().get(0).getTestuser()));
                    } else {
                        sheet.addCell(new Label(11, i + 1, ""));
                    }
                    if (recelist.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(12, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(12, i + 1, "否"));
                    }
                    if (recelist.get(i).getSmlist().get(0).getType() != null && !"".equals(recelist.get(i).getSmlist().get(0).getType())) {
                        if (recelist.get(i).getSmlist().get(0).getType().equals("a")) {
                            sheet.addCell(new Label(13, i + 1, "中心训练"));
                        } else if (recelist.get(i).getSmlist().get(0).getType().equals("b")) {
                            sheet.addCell(new Label(13, i + 1, "家庭训练"));
                        } else if (recelist.get(i).getSmlist().get(0).getType().contains("ab") || recelist.get(i).getSmlist().get(0).getType().contains("ac")) {
                            sheet.addCell(new Label(13, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(13, i + 1, ""));
                    }
                    if (recelist.get(i).getSmlist().get(0).getLesson() != null && !"".equals(recelist.get(i).getSmlist().get(0).getLesson() != null)) {
                        sheet.addCell(new Label(14, i + 1, String.valueOf(recelist.get(i).getSmlist().get(0).getLesson())));
                    } else {
                        sheet.addCell(new Label(14, i + 1, ""));
                    }
                    sheet.addCell(new Label(15, i + 1, recelist.get(i).getSmlist().get(0).getCard()));
                    sheet.addCell(new Label(16, i + 1, recelist.get(i).getSmlist().get(0).getSerial()));
                    sheet.addCell(new Label(17, i + 1, recelist.get(i).getSmlist().get(0).getDeadline()));
                    sheet.addCell(new Label(18, i + 1, recelist.get(i).getSmlist().get(0).getPrice()));
                    sheet.addCell(new Label(19, i + 1, recelist.get(i).getSmlist().get(0).getMoneylog()));
                }
                if (recelist.get(i).getSmlist() != null && recelist.get(i).getSmlist().size() > 1) {
                    sheet.addCell(new Label(10, i + 1, recelist.get(i).getSmlist().get(0).getKeyname()));
                    if (recelist.get(i).getSmlist().get(0).getTestuser() != null && recelist.get(i).getSmlist().get(0).getTestuser() != "") {
                        sheet.addCell(new Label(11, i + 1, recelist.get(i).getSmlist().get(0).getTestuser()));
                    } else {
                        sheet.addCell(new Label(11, i + 1, ""));
                    }
                    if (recelist.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(12, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(12, i + 1, "否"));
                    }
                    if (recelist.get(i).getSmlist().get(0).getType() != null && !"".equals(recelist.get(i).getSmlist().get(0).getType())) {
                        if (recelist.get(i).getSmlist().get(0).getType().equals("a")) {
                            sheet.addCell(new Label(13, i + 1, "中心训练"));
                        } else if (recelist.get(i).getSmlist().get(0).getType().equals("b")) {
                            sheet.addCell(new Label(13, i + 1, "家庭训练"));
                        } else if (recelist.get(i).getSmlist().get(0).getType().contains("ab") || recelist.get(i).getSmlist().get(0).getType().contains("ac")) {
                            sheet.addCell(new Label(13, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(13, i + 1, ""));
                    }
                    if (recelist.get(i).getSmlist().get(0).getLesson() != null && !"".equals(recelist.get(i).getSmlist().get(0).getLesson())) {
                        sheet.addCell(new Label(14, i + 1, String.valueOf(recelist.get(i).getSmlist().get(0).getLesson())));
                    } else {
                        sheet.addCell(new Label(14, i + 1, ""));
                    }
                    sheet.addCell(new Label(15, i + 1, recelist.get(i).getSmlist().get(0).getCard()));
                    sheet.addCell(new Label(16, i + 1, recelist.get(i).getSmlist().get(0).getSerial()));
                    sheet.addCell(new Label(17, i + 1, recelist.get(i).getSmlist().get(0).getDeadline()));
                    sheet.addCell(new Label(18, i + 1, recelist.get(i).getSmlist().get(0).getPrice()));
                    sheet.addCell(new Label(19, i + 1, recelist.get(i).getSmlist().get(0).getMoneylog()));

                    sheet.addCell(new Label(20, i + 1, recelist.get(i).getSmlist().get(1).getKeyname()));
                    if (recelist.get(i).getSmlist().get(0).getTestuser() != null && recelist.get(i).getSmlist().get(0).getTestuser() != "") {
                        sheet.addCell(new Label(21, i + 1, recelist.get(i).getSmlist().get(1).getTestuser()));
                    } else {
                        sheet.addCell(new Label(21, i + 1, ""));
                    }
                    if (recelist.get(i).getWorth() == 6) {
                        sheet.addCell(new Label(22, i + 1, "是"));
                    } else {
                        sheet.addCell(new Label(22, i + 1, "否"));
                    }
                    if (recelist.get(i).getSmlist().get(1).getType() != null && !"".equals(recelist.get(i).getSmlist().get(1).getType())) {
                        if (recelist.get(i).getSmlist().get(1).getType().equals("a")) {
                            sheet.addCell(new Label(23, i + 1, "中心训练"));
                        } else if (recelist.get(i).getSmlist().get(1).getType().equals("b")) {
                            sheet.addCell(new Label(23, i + 1, "家庭训练"));
                        } else if (recelist.get(i).getSmlist().get(1).getType().contains("ab") || recelist.get(i).getSmlist().get(1).getType().contains("ac")) {
                            sheet.addCell(new Label(23, i + 1, "中心家庭结合训练"));
                        }
                    } else {
                        sheet.addCell(new Label(23, i + 1, ""));
                    }
                    if (recelist.get(i).getSmlist().get(1).getLesson() != null && !"".equals(recelist.get(i).getSmlist().get(1).getLesson() != null)) {
                        sheet.addCell(new Label(24, i + 1, String.valueOf(recelist.get(i).getSmlist().get(1).getLesson())));
                    } else {
                        sheet.addCell(new Label(24, i + 1, ""));
                    }
                    sheet.addCell(new Label(25, i + 1, recelist.get(i).getSmlist().get(1).getCard()));
                    sheet.addCell(new Label(26, i + 1, recelist.get(i).getSmlist().get(1).getSerial()));
                    sheet.addCell(new Label(27, i + 1, recelist.get(i).getSmlist().get(1).getDeadline()));
                    sheet.addCell(new Label(28, i + 1, recelist.get(i).getSmlist().get(1).getPrice()));
                    sheet.addCell(new Label(29, i + 1, recelist.get(i).getSmlist().get(1).getMoneylog()));
                }
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }


    public static String writeExcelStudentByTime(List<TgReception> recelist, String type,String sedate) {
        String title[] = {"接待人", "测评中心", "学员姓名", "测评老师 ", "测评项目调查", "学员姓名2", "测评老师2", "测评项目调查2"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            if (type.equals("1")) {
                name = "客户学员测评项目调查("+sedate+").xls";
            } else {
                name = "客户学员测评项目调查.xls";
            }
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
         //   WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("学员测评项目调查", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);

            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
            titleFormat.setAlignment(Alignment.CENTRE);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);

            WritableFont writableFont = new WritableFont(WritableFont.createFont("Microsoft Yahei"), 10);
            WritableCellFormat writableCellFormat = new WritableCellFormat(writableFont);
            WritableCellFormat writable = new WritableCellFormat(writableFont);
            writableCellFormat.setWrap(true);
            writable.setVerticalAlignment(VerticalAlignment.CENTRE);
            writable.setAlignment(Alignment.CENTRE);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容（学员信息）

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            for (int i = 0; i < recelist.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, recelist.get(i).getReceptionns(), writable));
                sheet.addCell(new Label(1, i + 1, recelist.get(i).getRegionns(), writable));
                try {
                    if (recelist.get(i).getSmlist() != null && recelist.get(i).getSmlist().size() > 0) {
                        sheet.addCell(new Label(2, i + 1, recelist.get(i).getSmlist().get(0).getKeyname(), writable));
                        sheet.addCell(new Label(3, i + 1, recelist.get(i).getSmlist().get(0).getTestuser(), writable));
                        String[] a = recelist.get(i).getSmlist().get(0).getResult().replace("1", "是").replace("2", "否").replace("0", "未选择").split(",");
                        if (a != null) {
                            sheet.setColumnView(4, 60);
                            if (a[4].contains(":") && a[5].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师：" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else if (a[4].contains(":") && a[5].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：", writableCellFormat));
                            } else if (a[4].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else if (a[5].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：", writableCellFormat));
                            }
                            if (a[0].equals("未选择") && a[1].equals("未选择") && a[2].equals("未选择") && a[3].equals("未选择") && a[4].equals("未选择") && a[5].equals("未选择") && a[5].equals("未选择")) {
                                sheet.addCell(new Label(4, i + 1, "全部未选择", writableCellFormat));
                            }
                        }
                    }
                    if (recelist.get(i).getSmlist() != null && recelist.get(i).getSmlist().size() > 1) {
                        sheet.addCell(new Label(2, i + 1, recelist.get(i).getSmlist().get(0).getKeyname(), writable));
                        sheet.addCell(new Label(3, i + 1, recelist.get(i).getSmlist().get(0).getTestuser(), writable));
                        String[] a = recelist.get(i).getSmlist().get(0).getResult().replace("1", "是").replace("2", "否").replace("0", "未选择").split(",");
                        if (a != null) {
                            sheet.setColumnView(4, 60);
                            if (a[4].contains(":") && a[5].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师：" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else if (a[4].contains(":") && a[5].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：", writableCellFormat));
                            } else if (a[4].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + a[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else if (a[5].contains(":") && a[6].contains(":")) {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + a[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：" + a[6].split(":")[1], writableCellFormat));
                            } else {
                                sheet.addCell(new Label(4, i + 1, "1.孩子是否喜欢培训老师:" + a[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                        + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a[3] + "\n" + "5.是否和家长产生争执：" + a[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a[6] + "\n" + "错误描述：", writableCellFormat));
                            }
                            if (a[0].equals("未选择") && a[1].equals("未选择") && a[2].equals("未选择") && a[3].equals("未选择") && a[4].equals("未选择") && a[5].equals("未选择") && a[5].equals("未选择")) {
                                sheet.addCell(new Label(4, i + 1, "全部未选择", writableCellFormat));
                            }
                        }
                        if (recelist.get(i).getSmlist().get(1) != null && !"".equals(recelist.get(i).getSmlist().get(1))) {
                            sheet.addCell(new Label(5, i + 1, recelist.get(i).getSmlist().get(1).getKeyname(), writable));
                            sheet.addCell(new Label(6, i + 1, recelist.get(i).getSmlist().get(1).getTestuser(), writable));
                            if (recelist.get(i).getSmlist().get(1).getResult() != null && !"".equals(recelist.get(i).getSmlist().get(1).getResult())) {
                                String[] a1 = recelist.get(i).getSmlist().get(1).getResult().replace("1", "是").replace("2", "否").replace("0", "未选择").split(",");
                                if (a1 != null) {
                                    sheet.setColumnView(7, 60);
                                    if (a1[4].contains(":") && a1[5].contains(":") && a1[6].contains(":")) {
                                        sheet.addCell(new Label(7, i + 1, "1.孩子是否喜欢培训老师：" + a1[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                                + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a1[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a1[3] + "\n" + "5.是否和家长产生争执：" + a1[4] + "\n" + "争执原因：" + a1[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a1[5] + "\n" + "未完成原因：" + a1[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a1[6] + "\n" + "错误描述：" + a1[6].split(":")[1], writableCellFormat));
                                    } else if (a1[4].contains(":") && a1[5].contains(":")) {
                                        sheet.addCell(new Label(7, i + 1, "1.孩子是否喜欢培训老师：" + a1[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                                + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a1[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a1[3] + "\n" + "5.是否和家长产生争执：" + a1[4] + "\n" + "争执原因：" + a1[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a1[5] + "\n" + "未完成原因：" + a1[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a1[6] + "\n" + "错误描述：", writableCellFormat));
                                    } else if (a1[4].contains(":") && a1[6].contains(":")) {
                                        sheet.addCell(new Label(7, i + 1, "1.孩子是否喜欢培训老师：" + a1[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                                + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a1[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a1[3] + "\n" + "5.是否和家长产生争执：" + a1[4] + "\n" + "争执原因：" + a1[4].split(":")[1] + "\n" + "6.是否完成全部测评（备注原因）：" + a1[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a1[6] + "\n" + "错误描述：" + a1[6].split(":")[1], writableCellFormat));
                                    } else if (a1[5].contains(":") && a1[6].contains(":")) {
                                        sheet.addCell(new Label(7, i + 1, "1.孩子是否喜欢培训老师：" + a1[0] + "\n" + "2.软件测评出生年月日是否准确：" + a[1] + "\n" + ""
                                                + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a1[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a1[3] + "\n" + "5.是否和家长产生争执：" + a1[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a1[5] + "\n" + "未完成原因：" + a1[5].split(":")[1] + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a1[6] + "\n" + "错误描述：" + a1[6].split(":")[1], writableCellFormat));
                                    } else {
                                        sheet.addCell(new Label(7, i + 1, "1.孩子是否喜欢培训老师：" + a1[0] + "\n" + "2.软件测评出生年月日是否准确：" + a1[1] + "\n" + ""
                                                + "3.测评老师是否做了记录，并向咨询老师介绍了孩子详细的情况：" + a1[2] + "\n" + "4.咨询过程中，孩子是否没管住跑到咨询室了：" + a1[3] + "\n" + "5.是否和家长产生争执：" + a1[4] + "\n" + "争执原因：" + "\n" + "6.是否完成全部测评（备注原因）：" + a1[5] + "\n" + "未完成原因：" + "\n" + "7.脑电数据是否有较大错误（如太低或太高）：" + a1[6] + "\n" + "错误描述：", writableCellFormat));
                                    }
                                    if (a1[0].equals("未选择") && a1[1].equals("未选择") && a1[2].equals("未选择") && a1[3].equals("未选择") && a1[4].equals("未选择") && a1[5].equals("未选择") && a1[5].equals("未选择")) {
                                        sheet.addCell(new Label(7, i + 1, "全部未选择", writableCellFormat));
                                    }

                                }
                            }

                        }

                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    //客户资源下载
    public static String writeExcelClientByTime(List<TgClient> clist, String type, String sdate, String edate) {
        String title[] = {"登记日期", "客户名称", "学员", "有效", "省份", "城市", "具体地址", "客户来源", "客户类别", "电话", "邮箱", "QQ", "登记人", "负责人", "登记人中心"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            if (type.equals("1")) {
                name = "客户资源项目调查(" + sdate + "-" + edate + ").xls";
            } else {
                name = "客户资源项目调查.xls";
            }
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
       //     WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("客户资源调查", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < clist.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, clist.get(i).getDateTime()));
                sheet.addCell(new Label(1, i + 1, clist.get(i).getNickname()));
                sheet.addCell(new Label(2, i + 1, clist.get(i).getStudent()));
                if (clist.get(i).getInvalid() == 0) {
                    sheet.addCell(new Label(3, i + 1, "是"));
                } else {
                    sheet.addCell(new Label(3, i + 1, "否"));
                }
                sheet.addCell(new Label(4, i + 1, clist.get(i).getProvince()));
                sheet.addCell(new Label(5, i + 1, clist.get(i).getCity()));
                sheet.addCell(new Label(6, i + 1, clist.get(i).getAddress()));
                sheet.addCell(new Label(7, i + 1, clist.get(i).getSourcens()));
                sheet.addCell(new Label(8, i + 1, clist.get(i).getCategoryns()));
                sheet.addCell(new Label(9, i + 1, clist.get(i).getPhone()));
                sheet.addCell(new Label(10, i + 1, clist.get(i).getEmail()));
                sheet.addCell(new Label(11, i + 1, clist.get(i).getQq()));
                sheet.addCell(new Label(12, i + 1, clist.get(i).getRawuser()));
                sheet.addCell(new Label(13, i + 1, clist.get(i).getUeuser()));
                sheet.addCell(new Label(14, i + 1, clist.get(i).getClregion()));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    //客户缴费信息下载
    public static String writeExcelClientMoneyByTime(List<TgReception> list, String startTime, String endTime) {
        String title[] = {"测评中心", "接待日期", "交款日期", "交款方式", "交款账号", "交款金额", "扣除手续费后金额", "接待老师", "客户姓名", "联系方式", "备注"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "缴费信息" + startTime + "-" + endTime + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("缴费信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getRegionns()));
                sheet.addCell(new Label(1, i + 1, list.get(i).getGtime()));
                sheet.addCell(new Label(2, i + 1, sdf.format(list.get(i).getMoneydate())));


                sheet.addCell(new Label(3, i + 1, list.get(i).getName1()));
                sheet.addCell(new Label(4, i + 1, list.get(i).getBank_id1()));
                String name1 = list.get(i).getName1();
                String center = list.get(i).getRegionns();
                Double m = list.get(i).getMoneys();
                if (name1 != null && !name1.equals("")) {
                    if (name1.equals("POS刷卡") || name1.equals("POS刷卡（备用）")||name1.contains("POS刷卡（备用编号")) {
                        if (m != null) {
                            if (m > 4487) {
                                if (center != null) {
                                    if (center.contains("杭州景芳中心") || center.contains("南京雨花台中心")) {
                                        m = m - 30;
                                    } else {
                                        m = m - 35;
                                    }
                                } else {
                                    m = m - 35;
                                }

                            } else {
                                m = m - m * 0.0078;
                            }
                        }
                    }
                }
                sheet.addCell(new Label(6, i + 1, list.get(i).getMoneys() + ""));
                sheet.addCell(new Label(5, i + 1, m + ""));
                sheet.addCell(new Label(7, i + 1, list.get(i).getReceptionns()));
                sheet.addCell(new Label(8, i + 1, list.get(i).getNickname()));
                sheet.addCell(new Label(9, i + 1, list.get(i).getPhone()));
                sheet.addCell(new Label(10, i + 1, list.get(i).getBeizhu()));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    //预约到访下载
    public static String writeExcelyydfXz(List<TgUserinfo> uList, String time) {
        String title[] = {"中心地址", "预约人", "接待人个数", "总数"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "咨询师预约到访统计" + time + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
           // WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("咨询师预约到访统计", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(45);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(500);
            //设置字体s
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.WHITE);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < uList.size(); i++)  //context
            {
                Integer zon = uList.get(i).getZonCount();
                sheet.addCell(new Label(0, i + 1, uList.get(i).getRegions() + ""));
                sheet.addCell(new Label(1, i + 1, uList.get(i).getNickname() + ""));
                sheet.addCell(new Label(2, i + 1, uList.get(i).getTotrate() + ""));
                sheet.addCell(new Label(3, i + 1, zon + ""));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    //网络推广信息下载
    public static String writeExcelwltgXz(List<TgClient> list,String startTime, String endTime) {
        String title[] = {"客户", "登记人", "所在地", "渠道", "联系方式", "登记日期", "最新跟踪情况"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "网推信息" + startTime +"-"+ endTime + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("网推信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(30);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(500);
            //设置字体s
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.WHITE);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
            	sheet.addCell(new Label(0, i + 1, list.get(i).getNickname()));
                sheet.addCell(new Label(1, i + 1, list.get(i).getRawuser()));
                if(list.get(i).getCity()!=null&&!"".equals(list.get(i).getCity())){
                	
                	sheet.addCell(new Label(2, i + 1, list.get(i).getProvince()+list.get(i).getCity()));
                }else{
                	sheet.addCell(new Label(2, i + 1, list.get(i).getProvince()));
                }
                
                sheet.addCell(new Label(3, i + 1, list.get(i).getSourcens()));
                sheet.addCell(new Label(4, i + 1, list.get(i).getPhone())); 
                sheet.addCell(new Label(5, i + 1, list.get(i).getDateTime()));
                sheet.addCell(new Label(6, i + 1, list.get(i).getFallow()));
            	
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    
    

    //咨询师业绩周统计下载
    public static String writeExcelweekXz(List<TeaWeek> list, String startTime, String endTime) {
        String title[] = {"校区", "课程顾问", "带教老师", "新增业绩", "接待测评量", "成单数", "成单率", "业绩/接待测评量", "市场新增业绩", "市场接待测评量", "市场成单数", "市场成单率"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "咨询师业绩周统计" + startTime + "-" + endTime + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("缴费信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            Double money = 0.0;
            Double money2 = 0.0;
            Integer count1 = 0;
            Integer count2 = 0;
            Integer count3 = 0;
            Integer count4 = 0;
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getCenter()));

                sheet.addCell(new Label(1, i + 1, list.get(i).getNickname()));
                if (list.get(i).getNickname().equals("张海荣"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));
                else if (list.get(i).getNickname().equals("王欢"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));
                else if (list.get(i).getNickname().equals("王璇"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));
                else if (list.get(i).getNickname().equals("于佳昕"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));
                else if (list.get(i).getNickname().equals("刘一凡"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));
                else if (list.get(i).getNickname().equals("任丽萍"))
                    sheet.addCell(new Label(2, i + 1, "李周昕"));

                else if (list.get(i).getNickname().equals("史愉会"))

                    sheet.addCell(new Label(2, i + 1, "杜宁"));
                else if (list.get(i).getNickname().equals("吕喜明"))
                    sheet.addCell(new Label(2, i + 1, "杜宁"));

                else if (list.get(i).getNickname().equals("刘国超"))
                    sheet.addCell(new Label(2, i + 1, "孙嘉铭"));
                else if (list.get(i).getNickname().equals("杨坤"))
                    sheet.addCell(new Label(2, i + 1, "孙嘉铭"));
                else if (list.get(i).getNickname().equals("唐博"))
                    sheet.addCell(new Label(2, i + 1, "孙嘉铭"));
                else if (list.get(i).getNickname().equals("黄玲玲"))
                    sheet.addCell(new Label(2, i + 1, "孙嘉铭"));
                else if (list.get(i).getNickname().equals("杨艳"))
                    sheet.addCell(new Label(2, i + 1, "孙嘉铭"));

                else if (list.get(i).getNickname().equals("陈凯峰"))
                    sheet.addCell(new Label(2, i + 1, "王贇"));
                else if (list.get(i).getNickname().equals("王永青"))
                    sheet.addCell(new Label(2, i + 1, "王贇"));

                else if (list.get(i).getNickname().equals("魏丽"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));
                else if (list.get(i).getNickname().equals("周海霞"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));
                else if (list.get(i).getNickname().equals("侯霁珊"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));
                else if (list.get(i).getNickname().equals("张晓靖"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));
                else if (list.get(i).getNickname().equals("胡燕"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));
                else if (list.get(i).getNickname().equals("韩明聪"))
                    sheet.addCell(new Label(2, i + 1, "刘云"));

                else if (list.get(i).getNickname().equals("罗志鹏"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("杨晓军"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("闫培雯"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("梅贺芳"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("于雷"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("李海传"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("宋爱霞"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("贾雪"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("张伟"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));
                else if (list.get(i).getNickname().equals("李婷"))
                    sheet.addCell(new Label(2, i + 1, "龚琪雯"));

                else if (list.get(i).getNickname().equals("刘玉玲"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));
                else if (list.get(i).getNickname().equals("周漾"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));
                else if (list.get(i).getNickname().equals("陈红珍"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));
                else if (list.get(i).getNickname().equals("陈婷婷"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));
                else if (list.get(i).getNickname().equals("关章梅"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));
                else if (list.get(i).getNickname().equals("邹红娇"))
                    sheet.addCell(new Label(2, i + 1, "郑丽"));

                else if (list.get(i).getNickname().equals("贺丹"))
                    sheet.addCell(new Label(2, i + 1, "冯亮华"));
                else if (list.get(i).getNickname().equals("薛白羽"))
                    sheet.addCell(new Label(2, i + 1, "冯亮华"));
                else if (list.get(i).getNickname().equals("王会玲"))
                    sheet.addCell(new Label(2, i + 1, "冯亮华"));
                else if (list.get(i).getNickname().equals("佘晴"))
                    sheet.addCell(new Label(2, i + 1, "冯亮华"));

                else if (list.get(i).getNickname().equals("杨柳"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("杨洋"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("周志梅"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("胡月"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("高望"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("程兴富"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else if (list.get(i).getNickname().equals("李艳君"))
                    sheet.addCell(new Label(2, i + 1, "龚广杰"));
                else {
                    sheet.addCell(new Label(2, i + 1, ""));
                }
                sheet.addCell(new Label(3, i + 1, list.get(i).getXzMoneys() + ""));
                sheet.addCell(new Label(4, i + 1, list.get(i).getCpCount() + ""));
                sheet.addCell(new Label(5, i + 1, list.get(i).getJtCount() + ""));
                if (list.get(i).getCpCount() != null && list.get(i).getCpCount() != 0) {
                    sheet.addCell(new Label(6, i + 1, list.get(i).getJtCount() / list.get(i).getCpCount() + ""));
                } else {
                    sheet.addCell(new Label(6, i + 1, "0"));
                }
                if (list.get(i).getCpCount() != null && list.get(i).getCpCount() != 0) {
                    sheet.addCell(new Label(7, i + 1, list.get(i).getXzMoneys() / list.get(i).getCpCount() + ""));
                } else {
                    sheet.addCell(new Label(7, i + 1, "0"));
                }

                sheet.addCell(new Label(8, i + 1, list.get(i).getScMoney() + ""));
                sheet.addCell(new Label(9, i + 1, list.get(i).getScCount() + ""));
                sheet.addCell(new Label(10, i + 1, list.get(i).getSccdCount() + ""));

                if (list.get(i).getScCount() != null && list.get(i).getScCount() != 0) {
                    sheet.addCell(new Label(11, i + 1, list.get(i).getSccdCount() / list.get(i).getScCount() + ""));
                } else {
                    sheet.addCell(new Label(11, i + 1, "0"));
                }

                money = money + list.get(i).getXzMoneys();
                count1 = count1 + list.get(i).getCpCount();
                count2 = count2 + list.get(i).getJtCount();
                money2 = money2 + list.get(i).getScMoney();
                count3 = count3 + list.get(i).getScCount();
                count4 = count4 + list.get(i).getSccdCount();
            }
            sheet.addCell(new Label(0, list.size() + 1, "总计"));
            sheet.addCell(new Label(3, list.size() + 1, money + ""));
            sheet.addCell(new Label(4, list.size() + 1, count1 + ""));
            sheet.addCell(new Label(5, list.size() + 1, count2 + ""));
            sheet.addCell(new Label(7, list.size() + 1, money2 + ""));
            sheet.addCell(new Label(8, list.size() + 1, count3 + ""));
            sheet.addCell(new Label(9, list.size() + 1, count4 + ""));
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    //学生缴费信息下载
    public static String writeExcelMoneyByTime(List<TgMoney> list, String startTime, String endTime) {
        String title[] = {"学生", "课程类型", "缴费类型", "缴费中心", "交款日期", "交款方式", "交款账号", "交款金额", "总金额", "扣除手续费后金额", "接待老师", "联系方式", "备注", "状态", "摘要"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "学生缴费信息" + startTime + "-" + endTime + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("缴费信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                if (list.get(i).getStu_name() != null && !"".equals(list.get(i).getStu_name())) {
                    sheet.addCell(new Label(0, i + 1, list.get(i).getStu_name()));
                } else {
                    sheet.addCell(new Label(0, i + 1, list.get(i).getStudent()));
                }

                String pra = "";
                if (list.get(i).getPra_type() != null && !"".equals(list.get(i).getPra_type())) {
                    if (list.get(i).getPra_type() == 1) {
                        pra = "非脑电";
                        sheet.addCell(new Label(1, i + 1, "非脑电" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 2) {
                        pra = "普通中心";
                        sheet.addCell(new Label(1, i + 1, "普通中心" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 3) {
                        pra = "全脑";
                        sheet.addCell(new Label(1, i + 1, "全脑" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 4) {
                        pra = "太优";
                        sheet.addCell(new Label(1, i + 1, "太优" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 5) {
                        pra = "家庭训练";
                        sheet.addCell(new Label(1, i + 1, "家庭训练" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 6) {
                        pra = "夏令营";
                        sheet.addCell(new Label(1, i + 1, "夏令营" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 7) {
                        pra = "上门费";
                        sheet.addCell(new Label(1, i + 1, "上门费" + list.get(i).getSmf_c()));
                    } else if (list.get(i).getPra_type() == 8) {
                        pra = "暑期培优班（非在读）";
                        sheet.addCell(new Label(1, i + 1, "暑期培优班（非在读）" + list.get(i).getTimes()));
                    } else if (list.get(i).getPra_type() == 9) {
                        pra = "暑期培优班（在读）";
                        sheet.addCell(new Label(1, i + 1, "暑期培优班（在读）" + list.get(i).getTimes()));
                    }
                }

                if (list.get(i).getMoneyType1() != null) {
                    if (list.get(i).getMoneyType1() == 1) {
                        if (list.get(i).getMoneyType2() == 1) {
                            sheet.addCell(new Label(2, i + 1, "新增 定金"));
                        } else if (list.get(i).getMoneyType2() == 2) {
                            sheet.addCell(new Label(2, i + 1, "新增 部分款"));
                        } else if (list.get(i).getMoneyType2() == 3) {
                            sheet.addCell(new Label(2, i + 1, "新增 全款"));
                        } else if (list.get(i).getMoneyType2() == 4) {
                            sheet.addCell(new Label(2, i + 1, "新增 尾款"));
                        }
                    } else if (list.get(i).getMoneyType1() == 2) {
                        if (list.get(i).getMoneyType2() == 1) {
                            sheet.addCell(new Label(2, i + 1, "续费 定金"));
                        } else if (list.get(i).getMoneyType2() == 2) {
                            sheet.addCell(new Label(2, i + 1, "续费 部分款"));
                        } else if (list.get(i).getMoneyType2() == 3) {
                            sheet.addCell(new Label(2, i + 1, "续费 全款"));
                        } else if (list.get(i).getMoneyType2() == 4) {
                            sheet.addCell(new Label(2, i + 1, "续费 尾款"));
                        }
                    }
                }

                sheet.addCell(new Label(3, i + 1, list.get(i).getCenter()));
                sheet.addCell(new Label(4, i + 1, list.get(i).getMoneyDate()));
                sheet.addCell(new Label(5, i + 1, list.get(i).getMet_name()));
                sheet.addCell(new Label(6, i + 1, list.get(i).getBank_id()));
                sheet.addCell(new Label(7, i + 1, list.get(i).getMoneys() + ""));
                sheet.addCell(new Label(8, i + 1, list.get(i).getFp_moneys() + ""));
                String name1 = list.get(i).getMet_name();
                String center = list.get(i).getCenter();
                Double m = list.get(i).getMoneys();
                if (name1 != null && !name1.equals("")) {
                    if (name1.equals("POS刷卡") || name1.equals("POS刷卡（备用）")||name1.contains("POS刷卡（备用编号")) {
                        if (m != null) {
                            if (m > 4487) {
                                if (center != null) {
                                    if (center.contains("杭州景芳中心") || center.contains("南京雨花台中心") || center.contains("北京上地中心")
                                            || center.contains("天津小白楼中心") || center.contains("重庆南坪中心")) {
                                        m = m - 30;
                                    } else {
                                        m = m - 35;
                                    }
                                } else {
                                    m = m - 35;
                                }

                            } else {
                                m = m - m * 0.0078;
                            }
                        }
                    }
                }
                sheet.addCell(new Label(9, i + 1, m + ""));
                sheet.addCell(new Label(10, i + 1, list.get(i).getNickname1() + "," + list.get(i).getNickname2() + "," + list.get(i).getRecord_teacher()));
                sheet.addCell(new Label(11, i + 1, list.get(i).getPhone()));
                sheet.addCell(new Label(12, i + 1, list.get(i).getBeizhu()));
                if (list.get(i).getState() == 0) {
                    sheet.addCell(new Label(13, i + 1, "未审核"));
                } else if (list.get(i).getState() == 1) {
                    sheet.addCell(new Label(13, i + 1, "未通过"));
                } else if (list.get(i).getState() == 2) {
                    sheet.addCell(new Label(13, i + 1, "已通过"));
                }

                if (list.get(i).getStu_name() != null && !"".equals(list.get(i).getStu_name())) {
                    sheet.addCell(new Label(14, i + 1, list.get(i).getCenter() + list.get(i).getStu_name() + pra + "训练费用"));
                } else {
                    sheet.addCell(new Label(14, i + 1, list.get(i).getCenter() + list.get(i).getStudent() + pra + "训练费用"));
                }

            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }


    public static List<String> readExcel(File file) {
        List<String> list = new ArrayList<String>();
        try {
            InputStream is = new FileInputStream(file);
            Workbook rwb = Workbook.getWorkbook(is);
            //获取第一张Sheet表
            Sheet rst = rwb.getSheet(0);
            //获取Sheet表中所包含的总列数
            int rsColumns = rst.getColumns();
            //获取Sheet表中所包含的总行数
            int rsRows = rst.getRows();
            //获取指定单元格的对象引用
            for (int i = 0; i < rsRows; i++) {
                String content = "";
                for (int j = 0; j < rsColumns; j++) {
                    if (j == rsColumns - 1) {
                        content += rst.getCell(j, i).getContents();
                    } else {
                        content += rst.getCell(j, i).getContents() + ",";
                    }
                }
                list.add(content);
            }
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /***
     * 读取Excel.xls格式
     */
    public static List<String> readExcel(String filePath) {
        List<String> list = new ArrayList<String>();
        try {
            InputStream is = new FileInputStream(filePath);
            Workbook rwb = Workbook.getWorkbook(is);
            //获取第一张Sheet表
            Sheet rst = rwb.getSheet(0);
            //获取Sheet表中所包含的总列数
            int rsColumns = rst.getColumns();
            //获取Sheet表中所包含的总行数
            int rsRows = rst.getRows();
            //获取指定单元格的对象引用
            for (int i = 0; i < rsRows; i++) {
                String content = "";
                for (int j = 0; j < rsColumns; j++) {
                    if (j == rsColumns - 1) {
                        content += rst.getCell(j, i).getContents();
                    } else {
                        content += rst.getCell(j, i).getContents() + ",";
                    }
                }
                list.add(content);
            }
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public static void main(String[] args) {
        WriterExcel ex = new WriterExcel();
        //List<String> dataList = ex.readExcel("D:/mydata/uploadoa/doc/1298_20160904153635.xls");
        WriterExcel.writeExcelStudentByTime(null, "1","sedate");

    }

    public static void writeExcelenrollmentRecord(List<TgMoney> list, HttpServletResponse response, String stu_type, TgMoneyDao moneyDao) throws IOException {
        OutputStream output = response.getOutputStream();
        Map<Integer, String> moneyType2 = new HashMap<Integer, String>();
        Map<Integer, String> praType = new HashMap<Integer, String>();
        Map<Integer, String> status = new HashMap<Integer, String>();
        moneyType2.put(1, "定金");
        moneyType2.put(2, "部分款");
        moneyType2.put(3, "全款");
        moneyType2.put(4, "尾款");
        praType.put(1, "非脑电");
        praType.put(2, "普通中心");
        praType.put(3, "全脑");
        praType.put(4, "太优");
        praType.put(5, "家庭训练");
        praType.put(6, "夏令营");
        praType.put(7, "上门费");
        praType.put(8, "暑期培优班（非在读）");
        praType.put(9, "暑期培优班（在读）");
        status.put(0, "未审核");
        status.put(1, "未通过");
        status.put(2, "已通过");
        try {
            // 创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("学员缴费");
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeight((short) 500);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = wb.createFont();
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font.setFontName("微软雅黑");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
            font.setFontHeightInPoints((short) 18);// 设置字体大小
            style.setFont(font);
            sheet.setColumnWidth(0, 15 * 256);
            CellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font2 = wb.createFont();
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font2.setFontName("微软雅黑");
            font2.setFontHeightInPoints((short) 12);// 设置字体大小
            style2.setFont(font2);
            style2.setWrapText(true);// 设置自动换行
            // 在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(0);
            row2.setHeightInPoints(50);
            row2.createCell(0).setCellValue("交款中心");
            row2.createCell(1).setCellValue("定金日期");
            row2.createCell(2).setCellValue("交款时间");
            row2.createCell(3).setCellValue("收款金额");
            row2.createCell(4).setCellValue("缴费类型");
            row2.createCell(5).setCellValue("学员姓名");
            row2.createCell(6).setCellValue("课程类型");
            row2.createCell(7).setCellValue("接待老师");
            row2.createCell(8).setCellValue("来源");
            row2.createCell(9).setCellValue("状态");
            CellStyle style3 = wb.createCellStyle();
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font3 = wb.createFont();
            style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style3.setFont(font3);
            for (int i = 0; i < 10; i++) {
                if (row2.getCell(i) != null) {
                    row2.getCell(i).setCellStyle(style2);
                }
            }
            for (int i = 0; i < list.size(); i++) {
                List<TgMoney> depositDate = null;
                if (list.get(i).getStu_id() != null && list.get(i).getStu_id() != 0) {
                    //根据id查询定金日期
                    TgMoney money2 = new TgMoney();
                    money2.setStu_id(list.get(i).getStu_id());
                    depositDate = moneyDao.getDepositDate(money2);
                } else if (!StringUtil.isEmpty(list.get(i).getStu_name())) {
                    //根据姓名查询定金日期
                    TgMoney money2 = new TgMoney();
                    money2.setStudent(list.get(i).getStudent());
                    depositDate = moneyDao.getDepositDate(money2);
                }
                if (depositDate != null && depositDate.size() > 0) {
                    list.get(i).setDepositDate(depositDate.get(0).getMoneyDate());
                }
                sheet.setColumnWidth(i, 17 * 320);
                HSSFRow row4 = sheet.createRow(1 + i);
                row4.createCell(0).setCellValue(list.get(i).getCenter());
                row4.createCell(1).setCellValue((list.get(i).getDepositDate() == null && "".equals(list.get(i).getDepositDate())) ? "" : list.get(i).getDepositDate());
                row4.createCell(2).setCellValue(list.get(i).getMoneyDate());
                row4.createCell(3).setCellValue(list.get(i).getMoneys());
                row4.createCell(4).setCellValue(moneyType2.get(list.get(i).getMoneyType2()));
                row4.createCell(5).setCellValue(list.get(i).getStu_name() == null || list.get(i).getStu_name() == "" ? list.get(i).getStudent() : list.get(i).getStu_name());
                row4.createCell(6).setCellValue(praType.get(list.get(i).getPra_type()) + list.get(i).getTimes() + "次");
                row4.createCell(7).setCellValue(list.get(i).getNickname1());
                row4.createCell(8).setCellValue(list.get(i).getSourceName());
                row4.createCell(9).setCellValue(status.get(list.get(i).getState()));
                for (int j = 0; j < 10; j++) {
                    if (row4.getCell(j) != null) {
                        row4.getCell(j).setCellStyle(style3);
                    }
                }
            }
            String fileName = "";
            if (stu_type.equals("1")) {
                fileName = "新增学员";
            } else if (stu_type.equals("2")) {
                fileName = "续费学员";
            } else {
                fileName = "家庭学员";
            }

            // 输出Excel文件
            response.reset();
            response.setHeader(
                    "Content-disposition",
                    "attachment;filename="
                            + URLEncoder.encode("招生记录-" + fileName + ".xls", "UTF-8"));
            response.setContentType("application/msexcel;charset=UTF-8");
            wb.write(output);
        } catch (Exception e) {
            e.printStackTrace();
            PrintWriter out = response.getWriter();
            response.setContentType("text/html;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Cache-Control", "no-cache");
            out.println("<script language='javascript'>");
            out.println("alert('服务器发送错误！请稍后重试')");
            out.println("history.go(-1)");
            out.println("</script>");
            out.flush();
            out.close();
        } finally {
            output.close();
        }
    }

    /**
     * @Description:导出Excel 
     * @author: Hqd
     * @date: 2017年11月7日 下午3:26:42 
     * @return
     */
    public static void uploadWeekToExcel(String monday, String sunday, List<TgOffice> list, TgMoneyDao moneyDao, HttpServletResponse response) throws IOException {
        OutputStream output = response.getOutputStream();
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("学员缴费");
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeight((short) 500);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = wb.createFont();
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font.setFontName("微软雅黑");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
            font.setFontHeightInPoints((short) 18);// 设置字体大小
            style.setFont(font);
            sheet.setColumnWidth(0, 15 * 256);
            CellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font2 = wb.createFont();
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font2.setFontName("微软雅黑");
            font2.setFontHeightInPoints((short) 12);// 设置字体大小
            style2.setFont(font2);
            style2.setWrapText(true);// 设置自动换行
            // 在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(0);
            row2.setHeightInPoints(50);
            /*row2.createCell(0).setCellValue("中心("+monday+"至"+sunday+")");
            row2.createCell(1).setCellValue("本周定金个数");
			row2.createCell(2).setCellValue("本周定金金额（元）");
			row2.createCell(3).setCellValue("本周全款个数");
			row2.createCell(4).setCellValue("本周全款金额（元）");
			row2.createCell(5).setCellValue("本周补足训练全款个数");
			row2.createCell(6).setCellValue("补足全款金额（元）");
			row2.createCell(7).setCellValue("本周部分款个数");
			row2.createCell(8).setCellValue("本周部分款金额（元）");
			row2.createCell(9).setCellValue("续费个数");
			row2.createCell(10).setCellValue("续费金额（元）");*/

            row2.createCell(0).setCellValue("中心(" + monday + "至" + sunday + ")");
            row2.createCell(1).setCellValue("本周全款个数");
            row2.createCell(2).setCellValue("本周全款金额（元）");
            row2.createCell(3).setCellValue("本周定金个数");
            row2.createCell(4).setCellValue("本周定金金额（元）");
            row2.createCell(5).setCellValue("本周补足训练全款个数");
            row2.createCell(6).setCellValue("补足全款金额（元）");
            row2.createCell(7).setCellValue("本周部分款个数");
            row2.createCell(8).setCellValue("本周部分款金额（元）");
            row2.createCell(9).setCellValue("续费个数");
            row2.createCell(10).setCellValue("续费金额（元）");
            row2.createCell(11).setCellValue("合计");
            CellStyle style3 = wb.createCellStyle();
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font3 = wb.createFont();
            style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style3.setFont(font3);
            for (int i = 0; i < 12; i++) {
                if (row2.getCell(i) != null) {
                    row2.getCell(i).setCellStyle(style2);
                }
            }
            HashMap map = new HashMap();
            map.put("monday", monday);
            map.put("sunday", sunday);
            Integer count1 = 0;
            Integer count2 = 0;
            Integer count3 = 0;
            Integer count4 = 0;
            Integer count5 = 0;
            Double moneyCount1 = 0d;
            Double moneyCount2 = 0d;
            Double moneyCount3 = 0d;
            Double moneyCount4 = 0d;
            Double moneyCount5 = 0d;
            for (int i = 0; i < list.size(); i++) {
                Double moneyCount6 = 0d;//横向合计
                sheet.setColumnWidth(i, 20 * 320);
                HSSFRow row3 = sheet.createRow(1 + i);
                map.put("centerKey", list.get(i).getKeyname());
                row3.createCell(0).setCellValue(list.get(i).getName());//中心名称
                //本周全款个数 （新增全款,本周内定金+部分款+尾款）
                Integer fullPaymentCount = moneyDao.getFullPaymentCount(map);
                count1+=fullPaymentCount;
                row3.createCell(1).setCellValue(fullPaymentCount);
                //全款金额
                Double fullPaymentMoneys = moneyDao.getFullPaymentMoneys(map);
                //--不能加全款金额
                moneyCount1+=fullPaymentMoneys;
                moneyCount6+=fullPaymentMoneys;
                row3.createCell(2).setCellValue(fullPaymentMoneys);//本周全款金额

                map.put("moneyType1", 1);
                map.put("moneyType2", 1);
                Integer excelCountByDate = moneyDao.getExcelCountByDate(map);
                count2+=excelCountByDate;
                row3.createCell(3).setCellValue(excelCountByDate + "");//本周定金个数
                Double excelMoneyByDate = moneyDao.getExcelMoneyByDate(map);
                moneyCount2+=excelMoneyByDate;
                moneyCount6+=excelMoneyByDate;
                row3.createCell(4).setCellValue(excelMoneyByDate + "");//本周定金金额（元）*/

                map.put("moneyType1", 1);
                map.put("moneyType2", 4);
                Integer retainageCount = moneyDao.getRetainageCount(map);
                count3+=retainageCount;
                row3.createCell(5).setCellValue(retainageCount + "");//本周补足训练全款个数
                Double retainageMoneys = moneyDao.getRetainageMoneys(map);
                moneyCount3+=retainageMoneys;
                moneyCount6+=retainageMoneys;
                row3.createCell(6).setCellValue(retainageMoneys + "");//补足全款金额（元）


                map.put("moneyType1", 1);
                map.put("moneyType2", 2);
                Integer excelCountByDate1 = moneyDao.getExcelCountByDate(map);
                count4+=excelCountByDate1;
                row3.createCell(7).setCellValue(excelCountByDate1 + "");//本周部分款个数
                Double excelMoneyByDate1 = moneyDao.getExcelMoneyByDate(map);
                moneyCount4+=excelMoneyByDate1;
                moneyCount6+=excelMoneyByDate1;
                row3.createCell(8).setCellValue(excelMoneyByDate1 + "");//本周部分款金额（元）
                
                map.put("moneyType2", null);
                map.put("moneyType1", 2);
                Integer excelCountByDate2 = moneyDao.getExcelCountByDate(map);
                count5+=excelCountByDate2;
                row3.createCell(9).setCellValue(excelCountByDate2 + "");//续费个数
                Double excelMoneyByDate2 = moneyDao.getExcelMoneyByDate(map);
                moneyCount5+=excelMoneyByDate2;
                moneyCount6+=excelMoneyByDate2;
                row3.createCell(10).setCellValue(excelMoneyByDate2 + "");//续费金额（元）
				/*	List<TgMoney> fullPaymentStuIds = moneyDao.getFullPaymentStuId(map);
				List<TgMoney> fullPaymentStuNames = moneyDao.getFullPaymentStuName(map);
				count5 += fullPaymentStuIds.size();
				row3.createCell(1).setCellValue(fullPaymentStuIds.size()+fullPaymentStuNames.size());//续费金额（元）
				Double week5Money = 0d;
				if (fullPaymentStuIds!=null && fullPaymentStuIds.size()>0){
					StringBuffer stuIds  = new StringBuffer();
					for (int j= 0;j<fullPaymentStuIds.size();j++){
						stuIds.append(fullPaymentStuIds.get(j).getStu_id()+",");
					}
					map.put("stu_id",stuIds.substring(0,stuIds.length()-1));
					 week5Money += moneyDao.getFullPaymentMoney(map);
					moneyCount5+=week5Money;
					moneyCount6+=week5Money;
				}
				if (fullPaymentStuNames!=null && fullPaymentStuNames.size()>0){
					StringBuffer stuNames  = new StringBuffer();
					for (int j= 0;j<fullPaymentStuNames.size();j++){
						stuNames.append(fullPaymentStuNames.get(j).getStudent()+",");
					}
					map.put("student",stuNames.substring(0,stuNames.length()-1));
					week5Money += moneyDao.getFullPaymentMoney2(map);
					moneyCount5+=week5Money;
					moneyCount6+=week5Money;
				}
				row3.createCell(2).setCellValue(week5Money + "");*/
                row3.createCell(11).setCellValue(moneyCount6 + "");
                for (int j = 0; j < 12; j++) {
                    if (row3.getCell(j) != null) {
                        row3.getCell(j).setCellStyle(style3);
                    }
                }
            }
            HSSFRow row4 = sheet.createRow(1 + list.size());
            row4.createCell(0).setCellValue("总计");
            row4.createCell(1).setCellValue(count1);
            row4.createCell(2).setCellValue(moneyCount1);
            row4.createCell(3).setCellValue(count2);
            row4.createCell(4).setCellValue(moneyCount2);
            row4.createCell(5).setCellValue(count3);
            row4.createCell(6).setCellValue(moneyCount3);
            row4.createCell(7).setCellValue(count4);
            row4.createCell(8).setCellValue(moneyCount4);
            row4.createCell(9).setCellValue(count5);
            row4.createCell(10).setCellValue(moneyCount5);
            row4.createCell(11).setCellValue(moneyCount5 + moneyCount1 + moneyCount2 + moneyCount3 + moneyCount4);
            for (int j = 0; j < 12; j++) {
                if (row4.getCell(j) != null) {
                    row4.getCell(j).setCellStyle(style3);
                }
            }

            // 输出Excel文件
            response.reset();
            response.setHeader(
                    "Content-disposition",
                    "attachment;filename="
                            + URLEncoder.encode("训练定金及补足全款个数统计.xls", "UTF-8"));
            response.setContentType("application/msexcel;charset=UTF-8");
            wb.write(output);
        } catch (Exception e) {
            PrintWriter out = response.getWriter();
            response.setContentType("text/html;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Cache-Control", "no-cache");
            out.println("<script language='javascript'>");
            out.println("alert('服务器发送错误！请稍后重试')");
            out.println("history.go(-1)");
            out.println("</script>");
            out.flush();
            out.close();
            return ;
        } finally {
            output.close();
        }

    }

    public static String exportVoteExcel(HttpServletRequest request, HttpServletResponse response, JsVoteDao voteDao, BaseVo vo) throws IOException {
        try {
            Result result = new ResultSupport(true);
            String topic = request.getParameter("topic");
            String contact = request.getParameter("contact");
            String address = request.getParameter("address");
            String sname = request.getParameter("sname");
            String source = request.getParameter("source");
            String startTime = request.getParameter("stardate");
            String endTime = request.getParameter("enddate");
            String state = request.getParameter("state");
            String st_type = request.getParameter("st_type");

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

            JsVote vote = new JsVote();
            if (topic != null && !"".equals(topic)) {
                vote.setTopic(topic);
            }
            if (contact != null && !"".equals(contact)) {
                vote.setPhone(contact);
            }
            if (address != null && !"".equals(address)) {
                vote.setCity(address);
            }
            if (sname != null && !"".equals(sname)) {
                vote.setSpname(sname);
            }
            if (source != null && !"".equals(source)) {
                vote.setSource(source);
            }
            if (st_type != null && !"".equals(st_type)) {
                vote.setSt_type(Integer.valueOf(st_type));
            }
            if (startTime != null && !"".equals(startTime)) {
                result.setDefaultModel("startTime", startTime);
                try {
                    vote.setStartTime(sdf.parse(startTime).getTime() / 1000);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (endTime != null && !"".equals(endTime)) {
                result.setDefaultModel("endTime", endTime);
                try {
                    vote.setEndTime(sdf.parse(endTime).getTime() / 1000);
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            if (state != null && !"".equals(state)) {
                if (state.equals("1")) {
                    vote.setState1(state);
                } else if (state.equals("2")) {
                    vote.setState2(state);
                }
            }

            Integer currPage = 1;
            if (vo.getCurrPage() != null && !"".equals(vo.getCurrPage())) {
                currPage = vo.getCurrPage();
            }
            Integer total = voteDao.getVoCountByType(vote);

            if (total > 6000) {
                response.setContentType("text/html;charset=UTF-8");
                response.setCharacterEncoding("UTF-8");
                response.setHeader("Cache-Control", "no-cache");
                PrintWriter out = response.getWriter();
                out.println("<script language='javascript'>");
                out.println("alert('太多了，我实在导不出来!')");
                out.println("history.go(-1)");
                out.println("</script>");
                out.flush();
                out.close();
                out = null;
                return null;
            }
            Page page = PageUtil.createPage(total, currPage, total);
            List<JsVote> list = voteDao.getVolistByType(vote, page);

            SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy/MM/dd HH:mm");

            // 创建HSSFWorkbook对象(excel的文档对象)
            HSSFWorkbook wb = new HSSFWorkbook();
            // 建立新的sheet对象（excel的表单）
            HSSFSheet sheet = wb.createSheet("调查问卷");
            // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
            HSSFRow row1 = sheet.createRow(0);
            row1.setHeight((short) 500);
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font = wb.createFont();
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font.setFontName("微软雅黑");
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体增粗
            font.setFontHeightInPoints((short) 18);// 设置字体大小
            style.setFont(font);
            sheet.setColumnWidth(0, 15 * 256);
            CellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font2 = wb.createFont();
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            font2.setFontName("微软雅黑");
            font2.setFontHeightInPoints((short) 12);// 设置字体大小
            style2.setFont(font2);
            style2.setWrapText(true);// 设置自动换行
            // 在sheet里创建第二行
            HSSFRow row2 = sheet.createRow(0);
            row2.setHeightInPoints(50);
            // 创建单元格并设置单元格内容
            row2.createCell(0).setCellValue("问卷名称");
            row2.createCell(1).setCellValue("年龄");
            row2.createCell(2).setCellValue("城市");
            row2.createCell(3).setCellValue("联系方式");
            row2.createCell(4).setCellValue("分配状态");
            row2.createCell(5).setCellValue("来源渠道");
            row2.createCell(6).setCellValue("来源网址");
            row2.createCell(7).setCellValue("备注信息	");
            row2.createCell(8).setCellValue("提交日期	");

            for (int i = 0; i < 9; i++) {
                if (row2.getCell(i) != null) {
                    row2.getCell(i).setCellStyle(style2);
                }

            }
            CellStyle style3 = wb.createCellStyle();
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font3 = wb.createFont();
            style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style3.setFont(font3);

            // 百分数格式化
            for (int j = 0; j < list.size(); j++) {
                sheet.setColumnWidth(j, 15 * 320);
                HSSFRow row3 = sheet.createRow(1 + j);
                row3.createCell(0).setCellValue(list.get(j).getTopic() + "");
                row3.createCell(1).setCellValue(list.get(j).getAge() + "");
                row3.createCell(2).setCellValue(list.get(j).getCity() + "");
                row3.createCell(3).setCellValue(list.get(j).getPhone() + "");
                row3.createCell(4).setCellValue(list.get(j).getNickname() + "");
                row3.createCell(5).setCellValue(list.get(j).getSourcens() + "");
                row3.createCell(6).setCellValue(list.get(j).getUrl() + "");
                row3.createCell(7).setCellValue(list.get(j).getDesc1() == null ? "" : list.get(j).getDesc1());
                row3.createCell(8).setCellValue(sdf1.format(new Date(list.get(j).getCdate() * 1000)));
                for (int i = 0; i < 9; i++) {
                    if (row3.getCell(i) != null) {
                        row3.getCell(i).setCellStyle(style3);
                    }
                }
            }
            CellStyle style4 = wb.createCellStyle();
            style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            HSSFFont font4 = wb.createFont();
            font4.setColor(HSSFColor.RED.index);// HSSFColor.VIOLET.index
            // //字体颜色
            style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            style4.setFont(font4);
            style4.setWrapText(true);// 设置自动换行
            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            response.setHeader(
                    "Content-disposition",
                    "attachment;filename="
                            + URLEncoder.encode("调查问卷.xls", "UTF-8"));
            response.setContentType("application/msexcel;charset=UTF-8");
            wb.write(output);
            output.close();

        } catch (Exception e) {
            response.setContentType("text/html;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Cache-Control", "no-cache");
            PrintWriter out = response.getWriter();
            out.println("<script language='javascript'>");
            out.println("alert('导出失败!')");
            out.println("history.go(-1)");
            out.println("</script>");
            out.flush();
            out.close();
            out = null;
            return null;
        }
        return null;
    }

    /**
     * 未成单数下载
     */
    public static String writeExcelSingular(List<TgOffice> officeList, String startTime, String endTime, TgReceptionDao receptionDao,TgOfficeDao tgOfficeDao,MoneyService moneyService) {
        String title[] = {"中心", "实际到访数	", "成单数", "未成单数"};
        String name = "";
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            TgReception reception = new TgReception();
            if (StringUtil.isEmpty(startTime) || StringUtil.isEmpty(endTime)) {
                //获取当前月第一天：
                Calendar c = Calendar.getInstance();
                c.add(Calendar.MONTH, 0);
                c.set(Calendar.DAY_OF_MONTH, 1);//设置为1号,当前日期既为本月第一天
                String first = sdf.format(c.getTime());
                startTime = first;
                //System.out.println("===============first:" + first);
                reception.setStartTime(sdf.parse(startTime).getTime() / 1000);
                //获取当前月最后一天
                Calendar ca = Calendar.getInstance();
                ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));
                String last = sdf.format(ca.getTime());
                //System.out.println("===============last:" + last);
                endTime = last;
                reception.setEndTime(sdf.parse(last).getTime() / 1000);
            } else {
                reception.setStartTime(sdf.parse(startTime).getTime() / 1000);
                reception.setEndTime(sdf.parse(endTime).getTime() / 1000);
            }
            //t.xls为要新建的文件名
            name = "未成单数(" + startTime + "-" + endTime + ").xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
      //      WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));

            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet(name, 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(30);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(300);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.WHITE);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            TgOffice office = new TgOffice();
            office.setStaTime(sdf.parse(startTime).getTime()/1000);
            office.setEnTime(sdf.parse(endTime).getTime()/1000);
            
            TgMoney money = new TgMoney();
            TgMoney money1 = new TgMoney();
            
			money.setStartTime(startTime);
			money.setEndTime(sdf.format(DateUtil.getTommorrowDay1(sdf.parse(endTime))));
			money.setMoneyType1(1);
			
			money1.setEndTime(sdf.format(DateUtil.getTommorrowDay1(sdf.parse(endTime))));
			
            for (int i = 0; i < officeList.size(); i++)  //context
            {
                reception.setRegion(officeList.get(i).getKeyname());
                sheet.addCell(new Label(0, i + 1, officeList.get(i).getName() + ""));
                //实际到访数
                office.setRegion(officeList.get(i).getKeyname());
                Integer receTotalByExcel = tgOfficeDao.getCenterReal(office);//实际到访数
                
                money.setJd_center_key(officeList.get(i).getKeyname());
                Integer receSuccessTotalByExcel =moneyService.getZonCdByMoneys(money, money1, sdf.parse(startTime), sdf.parse(endTime));
					
                Integer receFailByExcel = receTotalByExcel - receSuccessTotalByExcel;
                sheet.addCell(new Label(1, i + 1, receTotalByExcel + ""));
                sheet.addCell(new Label(2, i + 1, receSuccessTotalByExcel + ""));
                sheet.addCell(new Label(3, i + 1, receFailByExcel + ""));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }


    public static String writeFlowInformation(List<TgMoney> moneyList, List<TgMoney> moneyList2) {
        String title[] = {"科目名称", "确切收款日期", "摘要", "借方本位币", "贷方本位币", "本位币余额/记账人", "渠道", "收款帐号"};
        String name = "";
        try {
            //t.xls为要新建的文件名
            name = "流水.xls";
            Map<Integer, String> praType = new HashMap<Integer, String>();
            praType.put(1, "非脑电");
            praType.put(2, "普通中心");
            praType.put(3, "全脑");
            praType.put(4, "太优");
            praType.put(5, "家庭训练");
            praType.put(6, "夏令营");
            praType.put(7, "上门费");
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            TgUser tgUser = UserUtil.getCookieUser();
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet(name, 0);
            WritableSheet sheet2 = book.createSheet(name + "(太优)", 1);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(30);
            sheet2.getSettings().setDefaultColumnWidth(30);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(300);
            sheet2.getSettings().setDefaultRowHeight(300);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.WHITE);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
                sheet2.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < moneyList.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, "收入"));
                sheet.addCell(new Label(1, i + 1, moneyList.get(i).getMoneyDate() + ""));
                sheet.addCell(new Label(2, i + 1, moneyList.get(i).getCenter() + moneyList.get(i).getStudent() + praType.get(moneyList.get(i).getPra_type()) + "费用"));
                sheet.addCell(new Label(3, i + 1, moneyList.get(i).getMoneys() + ""));
                sheet.addCell(new Label(4, i + 1, ""));
                sheet.addCell(new Label(5, i + 1, tgUser.getNickname() + ""));
                sheet.addCell(new Label(6, i + 1, moneyList.get(i).getMet_name() + ""));
                sheet.addCell(new Label(7, i + 1, moneyList.get(i).getBank_id() + ""));
            }
            for (int i = 0; i < moneyList2.size(); i++)  //context
            {
                sheet2.addCell(new Label(0, i + 1, "收入"));
                sheet2.addCell(new Label(1, i + 1, moneyList2.get(i).getMoneyDate() + ""));
                sheet2.addCell(new Label(2, i + 1, moneyList2.get(i).getCenter() + moneyList.get(i).getStudent() + praType.get(moneyList.get(i).getPra_type()) + "费用"));
                sheet2.addCell(new Label(3, i + 1, moneyList2.get(i).getMoneys() + ""));
                sheet2.addCell(new Label(4, i + 1, ""));
                sheet2.addCell(new Label(5, i + 1, tgUser.getNickname() + ""));
                sheet2.addCell(new Label(6, i + 1, moneyList2.get(i).getMet_name() + ""));
                sheet2.addCell(new Label(7, i + 1, moneyList2.get(i).getBank_id() + ""));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    //课程顾问绩效
    public static String writeExcelJixiao(List<JiXiao> list, String startTime) {
        String title[] = {"课程老师", "当月接待成交率得分", "当月接待成交率得分核算方法", "当月邀请实际到访完成率得分", "当月邀请实际到访完成率得分核算方法", "中心评分最高分", "评分详情", "总得分", "绩效工资基准值", "实发绩效工资"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "课程顾问绩效" + startTime + "-" + new Date().getTime()/100 + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            DecimalFormat df = new DecimalFormat("######0.0");   
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("缴费信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(13);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getName()));
                if(list.get(i).getCjl_df()!=null){
                	 sheet.addCell(new Label(1, i + 1, df.format(list.get(i).getCjl_df())));
                }else{
                	 sheet.addCell(new Label(1, i + 1, ""));
                }
               
                sheet.setColumnView(4, 60);
                sheet.addCell(new Label(2, i + 1, list.get(i).getCjl_gongs()));
                if(list.get(i).getDfl_df()!=null){
                	sheet.addCell(new Label(3, i + 1, df.format(list.get(i).getDfl_df())+""));
                }else{
                	sheet.addCell(new Label(3, i + 1, "0"));
                }
                
                sheet.setColumnView(4, 60);
                sheet.addCell(new Label(4, i + 1, list.get(i).getDfl_gongsi()));
                sheet.setColumnView(4, 60);
                sheet.addCell(new Label(5, i + 1, list.get(i).getZg_f()));
                sheet.setColumnView(4, 60);
                sheet.addCell(new Label(6, i + 1, list.get(i).getZr_pf()));
                if(list.get(i).getZonfen()!=null){
                	sheet.addCell(new Label(7, i + 1, df.format(list.get(i).getZonfen())));
                }else{
                	sheet.addCell(new Label(7, i + 1, "0"));
                }
                
                sheet.addCell(new Label(8, i + 1, list.get(i).getJzz()));
                if(list.get(i).getMoney()!=null){
                	sheet.addCell(new Label(9, i + 1, df.format(list.get(i).getMoney())));
                }else{
                	sheet.addCell(new Label(9, i + 1, "0"));
                }
                
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    //销售主管绩效下载
    public static String writeExcelJixiao2(List<JiXiao> list, String startTime) {
        String title[] = {"销售主管", "团队平均个人接待成交率得分","核算方法","当月团队邀约实际到访完成率得分","核算方法","总部领导评分最高分","总部领导打分","绩效工资基准值","总得分","实发绩效工资","右边是个人","当月接待成交率得分", "当月接待成交率得分核算方法", "当月邀请实际到访完成率得分", "当月邀请实际到访完成率得分核算方法"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "销售主管绩效" + startTime + "-" + new Date().getTime()/100 + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            DecimalFormat df = new DecimalFormat("######0.0");   
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("缴费信息", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(40);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getName()));
                if(list.get(i).getTd_cdl()!=null){
                	sheet.addCell(new Label(1, i + 1, df.format(list.get(i).getTd_cdl())));
                }else{
                	sheet.addCell(new Label(1, i + 1, "0"));
                }
                sheet.addCell(new Label(2, i + 1, list.get(i).getGongs1()));
                if(list.get(i).getTd_sdl()!=null){
                	sheet.addCell(new Label(3, i + 1, df.format(list.get(i).getTd_sdl())));
                }else{
                	sheet.addCell(new Label(3, i + 1, "0"));
                }
                sheet.addCell(new Label(4, i + 1, list.get(i).getGongs2()));
                sheet.addCell(new Label(5, i + 1, "20"));
                sheet.addCell(new Label(6, i + 1, list.get(i).getZr_pf()));
                sheet.addCell(new Label(7, i + 1, list.get(i).getJzz()));
                if(list.get(i).getFenshu()!=null && !"".equals(list.get(i).getFenshu())){
                	sheet.addCell(new Label(8, i + 1, df.format(Double.valueOf(list.get(i).getFenshu()))));
                }else{
                	sheet.addCell(new Label(8, i + 1, "0"));
                }
                
                if(list.get(i).getMoneys()!=null){
                	sheet.addCell(new Label(9, i + 1, df.format(list.get(i).getMoneys())));
                }else{
                	sheet.addCell(new Label(9, i + 1, "0"));
                }
                
                if(list.get(i).getCjl_df()!=null){
                	sheet.addCell(new Label(11, i + 1, df.format(list.get(i).getCjl_df())));
                }else{
                	sheet.addCell(new Label(11, i + 1, "0"));
                }
                
                sheet.addCell(new Label(12, i + 1, list.get(i).getCjl_gongs()));
                
                if(list.get(i).getDfl_df()!=null){
                	sheet.addCell(new Label(13, i + 1, df.format(list.get(i).getDfl_df())+""));
                }else{
                	sheet.addCell(new Label(13, i + 1, "0"));
                }
                
                sheet.addCell(new Label(14, i + 1, list.get(i).getDfl_gongsi()));
                
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    
    //流水记录下载
    public static String writeExcelLiushuiXz(JSONArray listObj,net.sf.json.JSONObject yueObj, String startTime) {
        String title[] = {"老师姓名", "中心地址","科目名称","收款/付款日期","摘要","金额","备注","审核状态"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "内部流水记录" + "-" + new Date().getTime()/100 + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("内部流水记录", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(40);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < listObj.size(); i++){
            	JSONObject o = new JSONObject(listObj.get(i).toString());
            	if(o.getString("is_has").equals("1")){
            		sheet.addCell(new Label(0, i + 1, o.getString("teacher")));
            		sheet.addCell(new Label(1, i + 1, o.getString("tea_center")));
            	}else{
            		sheet.addCell(new Label(0, i + 1, o.getString("tea_name")));
            		sheet.addCell(new Label(1, i + 1, o.getString("center")));
            	}
                
            	if(o.getString("type").equals("1")){
            		sheet.addCell(new Label(2, i + 1, "收入"));
            	}else{
            		sheet.addCell(new Label(2, i + 1, "支出"));
            	}
                
            	sheet.addCell(new Label(3, i + 1, o.getString("date")));
            	sheet.addCell(new Label(4, i + 1, o.getString("zhaiyao")));
            	sheet.addCell(new Label(5, i + 1, o.getString("moneys")));
            	sheet.addCell(new Label(6, i + 1, o.getString("beizhu")));
            	sheet.addCell(new Label(7, i + 1, o.getString("zhaiyao")));
            	sheet.addCell(new Label(8, i + 1, o.getString("beizhu")));
            	
            	if(o.getString("state").equals("0")){
            		sheet.addCell(new Label(9, i + 1, "未审核"));
            	}else{
            		sheet.addCell(new Label(9, i + 1, "已审核"));
            	}
            }
            sheet.addCell(new Label(0, listObj.size() + 1, "余额"));
            sheet.addCell(new Label(1, listObj.size() + 1, yueObj.getString("moneys").toString()));
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    //带宽记录下载
    public static String writeDaikuanXz(List<TgDaikuan> list) {
        String title[] = {"中心名称", "地址","办理时间","续费开始日期","续费时长","费用","供应商（ISP）","带宽","宽带类型","联系方式","联系方式","资费说明","宽带信息"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
            name = "带宽记录" + "-" + new Date().getTime()/100 + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("内部流水记录", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(40);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++){
            	sheet.addCell(new Label(0, i + 1, list.get(i).getCenter()));
            	sheet.addCell(new Label(1, i + 1, list.get(i).getAddress()));
            	sheet.addCell(new Label(2, i + 1, list.get(i).getDate()));
            	sheet.addCell(new Label(3, i + 1, list.get(i).getXf_date()));
            	if(list.get(i).getYear()==null || "".equals(list.get(i).getYear())){
            		sheet.addCell(new Label(4, i + 1, list.get(i).getYear()+"月"));
            	}else if(list.get(i).getMonth()==null || "".equals(list.get(i).getMonth())){
            		sheet.addCell(new Label(4, i + 1, list.get(i).getYear()+"年"));
            	}else{
            		sheet.addCell(new Label(4, i + 1, list.get(i).getYear()+"年"+list.get(i).getYear()+"月"));
            	}
            	
            	sheet.addCell(new Label(5, i + 1, list.get(i).getMoneys()+""));
            	sheet.addCell(new Label(6, i + 1, list.get(i).getCompany()));
            	sheet.addCell(new Label(7, i + 1, list.get(i).getDaikuan()+"M"));
            	sheet.addCell(new Label(8, i + 1, list.get(i).getDk_type()));
            	sheet.addCell(new Label(9, i + 1, list.get(i).getPhone()));
            	sheet.addCell(new Label(10, i + 1, list.get(i).getM_msg()));
            	sheet.addCell(new Label(11, i + 1, list.get(i).getDk_msg()));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    //离职员工下载
    public static String writeQuit(List<TgQuit> list) {
        String title[] = {"中心名称", "姓名","性别","岗位","入职时间","离职时间","离职类型","离职原因","是否按照基本工资发放","联系方式","物品发放","是否有工作服","是否退社保","备注","离职经办人"};
        String name = "";
        //操作执行
        try {
        	  SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
        	//t.xls为要新建的文件名
            name = "离职员工" + "-" + new Date().getTime()/100 + ".xls";
             WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
             //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("离职员工", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(40);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++){
            	sheet.addCell(new Label(0, i + 1, list.get(i).getCenter()));
            	sheet.addCell(new Label(1, i + 1, list.get(i).getUsername()));
            	if(list.get(i).getSex()!=null){
            		if(list.get(i).getSex()==1){
            			sheet.addCell(new Label(2, i + 1, "男"));
            		}else{
            			sheet.addCell(new Label(2, i + 1, "女"));
            		}
            		
            	}
            	sheet.addCell(new Label(3, i + 1, list.get(i).getPost()));
            	if(list.get(i).getInduction_time()!=null){
            		sheet.addCell(new Label(4, i + 1, sdf.format(list.get(i).getInduction_time())));
            	}
            	if(list.get(i).getDeparture_time()!=null){
            		sheet.addCell(new Label(5, i + 1, sdf.format(list.get(i).getDeparture_time())));
            	}
                if(list.get(i).getType()!=null){
                	if(list.get(i).getType()==1){
                		sheet.addCell(new Label(6, i + 1, "试用期主动提出离职"));
                	}else if(list.get(i).getType()==2){
                		sheet.addCell(new Label(6, i + 1, "合同期主动辞职"));
                	}else if(list.get(i).getType()==3){
                		sheet.addCell(new Label(6, i + 1, "劳动合同到期公司不续签"));
                	}else if(list.get(i).getType()==4){
                		sheet.addCell(new Label(6, i + 1, "老师合同期到期员工不续签"));
                	}else if(list.get(i).getType()==5){
                		sheet.addCell(new Label(6, i + 1, "协商解除合同"));
                	}
                }
            	sheet.addCell(new Label(7, i + 1, list.get(i).getReason()));
            	if(list.get(i).getPaid_in_accordance()!=null){
            		if(list.get(i).getPaid_in_accordance()==1){
            			sheet.addCell(new Label(8, i + 1, "是"));
            		}else{
            			sheet.addCell(new Label(8, i + 1, "否"));
            		}
            	}
            	
            	sheet.addCell(new Label(9, i + 1, list.get(i).getPhone()));
            	sheet.addCell(new Label(10, i + 1, list.get(i).getGood()));
            	if(list.get(i).getWork_clothes()!=null){
            		if(list.get(i).getWork_clothes()==1){
            			sheet.addCell(new Label(11, i + 1, "是"));
            		}else{
            			sheet.addCell(new Label(11, i + 1, "否"));
            		}
            	}
            	
            	if(list.get(i).getSocial_security()!=null){
            		if(list.get(i).getSocial_security()==1){
            			sheet.addCell(new Label(12, i + 1,"是"));
            		}else{
            			sheet.addCell(new Label(12, i + 1,"否"));
            		}
            	}
            	sheet.addCell(new Label(13, i + 1, list.get(i).getBeizhu()));
            	sheet.addCell(new Label(14, i + 1, list.get(i).getFill_in_nickname()));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    public static String writeSurvey(List<TgSatisSurvey> list) {
        String title[] = {"公司制度", "说明","领导管理","说明","气氛和氛围","说明","当前工作","说明","其他意见和建议"};
        String name = "";
        //操作执行
        try {
        	  
        	//t.xls为要新建的文件名
            name = "员工满意度" + "-" + new Date().getTime()/100 + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
         // WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("离职员工", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(40);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++){
            	if(list.get(i).getSatis_degree1()!=null){
            		if(list.get(i).getSatis_degree1()==1){
            			sheet.addCell(new Label(0, i + 1, "满意"));
            		}else if(list.get(i).getSatis_degree1()==2){
            			sheet.addCell(new Label(0, i + 1, "一般"));
            		}else if(list.get(i).getSatis_degree1()==3){
            			sheet.addCell(new Label(0, i + 1, "不满意"));
            		}
            		
            	}
            	sheet.addCell(new Label(1, i + 1, list.get(i).getSatis_degree_evaluate1()));
            	if(list.get(i).getSatis_degree2()!=null){
            		if(list.get(i).getSatis_degree2()==1){
            			sheet.addCell(new Label(2, i + 1, "满意"));
            		}else if(list.get(i).getSatis_degree2()==2){
            			sheet.addCell(new Label(2, i + 1, "一般"));
            		}else if(list.get(i).getSatis_degree2()==3){
            			sheet.addCell(new Label(2, i + 1, "不满意"));
            		}
            		 
            	}
            	sheet.addCell(new Label(3, i + 1, list.get(i).getSatis_degree_evaluate2()));
            	if(list.get(i).getSatis_degree3()!=null){
            		if(list.get(i).getSatis_degree3()==1){
            			sheet.addCell(new Label(4, i + 1, "满意"));
            		}else if(list.get(i).getSatis_degree3()==2){
            			sheet.addCell(new Label(4, i + 1, "一般"));
            		}else if(list.get(i).getSatis_degree3()==3){
            			sheet.addCell(new Label(4, i + 1, "不满意"));
            		}
            	  
            	}
            	sheet.addCell(new Label(5, i + 1, list.get(i).getSatis_degree_evaluate3()));
            	if(list.get(i).getSatis_degree4()!=null){
            		if(list.get(i).getSatis_degree4()==1){
            			sheet.addCell(new Label(6, i + 1, "满意"));
            		}else if(list.get(i).getSatis_degree4()==2){
            			sheet.addCell(new Label(6, i + 1, "一般"));
            		}else if(list.get(i).getSatis_degree4()==3){
            			sheet.addCell(new Label(6, i + 1, "不满意"));
            		}
            	}
            	sheet.addCell(new Label(7, i + 1, list.get(i).getSatis_degree_evaluate4()));
            	sheet.addCell(new Label(8, i + 1, list.get(i).getSatis_degree_evaluate5()));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }

    
    //网站调查资源下载
    public static String webwenjuan(List<JsFromUser> list, String startTime,String endTime) {
        String title[] = {"姓名", "年龄", "电话", "城市", "分类", "分配状态", "来源网址", "IP地址", "提交时间", "提交次数"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
        	name = "网站问卷" + startTime +"-"+ endTime + ".xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("客户资源调查", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(30);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getSchool()+""));
                sheet.addCell(new Label(1, i + 1, list.get(i).getAge()+""));
                sheet.addCell(new Label(2, i + 1, "电话"+list.get(i).getPhone()+""+"--邮箱"+list.get(i).getEmail()+""));
                sheet.addCell(new Label(3, i + 1, list.get(i).getCity()+""));
                sheet.addCell(new Label(4, i + 1, list.get(i).getType()+""));
                if (list.get(i).getUsername()!=null||!"".equals(list.get(i).getUsername())) {
                    sheet.addCell(new Label(5, i + 1, list.get(i).getUsername()+""));
                } else {
                    sheet.addCell(new Label(5, i + 1, "未分配"));
                }
                sheet.addCell(new Label(6, i + 1, list.get(i).getUrl()+""));
                sheet.addCell(new Label(7, i + 1, list.get(i).getIp()+""));
                sheet.addCell(new Label(8, i + 1, list.get(i).getcDate()+""));
                sheet.addCell(new Label(9, i + 1, list.get(i).getFrom_cn()+""));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    
  //物品资源统计下载
    public static String writeExcelWup(List<TgGoodProvide> list, String center) {
        String title[] = {"中心", "姓名", "入职日期", "职位", "所属部门", "发放物品", "登记日期", "经手人", "备注"};
        String name = "";
        //操作执行
        try {
            //t.xls为要新建的文件名
        	if(center!=null && !"".equals(center)){
        		name =  center +"物品发放" + ".xls";
        	}
        	name = "物品发放.xls";
            WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
            //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("物品发放", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(30);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,
                    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < list.size(); i++)  //context
            {
                sheet.addCell(new Label(0, i + 1, list.get(i).getCenter()+""));
                sheet.addCell(new Label(1, i + 1, list.get(i).getUsername()+""));
                sheet.addCell(new Label(2, i + 1, list.get(i).getDate_of_entry()+""));
                sheet.addCell(new Label(3, i + 1, list.get(i).getPost()+""));
                sheet.addCell(new Label(4, i + 1, list.get(i).getDepartment()+""));
                sheet.addCell(new Label(5, i + 1, list.get(i).getGood()+""));
                sheet.addCell(new Label(6, i + 1, list.get(i).getDate_of_regist()+""));
                sheet.addCell(new Label(7, i + 1, list.get(i).getHandle_person()+""));
                sheet.addCell(new Label(8, i + 1, list.get(i).getBeizhu()+""));
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
    }
    
    //员工情况表下载
	public static String writerecruitment(List<TgRecruitmentPeople> recruitment) {
		String title[] = {"工作地区", "岗位", "姓名", "邀约老师", "入司日期", "试用期工资", "试用期限", "联系电话", "异动情况", "中国银行卡号"};
		String name = "";
        //操作执行
        try {
        	  SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");
        	//t.xls为要新建的文件名
            name = "新员工情况表" + "-" + new Date().getTime()/100 + ".xls";
             WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
             //WritableWorkbook book= Workbook.createWorkbook(new File("D:/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("新员工情况", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(20);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < recruitment.size(); i++){
            	sheet.addCell(new Label(0, i + 1, recruitment.get(i).getCenter()+""));
            	sheet.addCell(new Label(1, i + 1, recruitment.get(i).getPost()+""));
            	sheet.addCell(new Label(2, i + 1, recruitment.get(i).getName()+""));
            	sheet.addCell(new Label(3, i + 1, recruitment.get(i).getUsername1()+""));
            	if(recruitment.get(i).getIncomecompanytime()!=null){
            		sheet.addCell(new Label(4, i + 1, recruitment.get(i).getIncomecompanytime()+""));
            	}else{
            		sheet.addCell(new Label(4, i + 1, "无"));
            	}
            	sheet.addCell(new Label(5, i + 1, recruitment.get(i).getEmployeeperiod()+""));
            	if(recruitment.get(i).getProbationperiod()!=null){
            		sheet.addCell(new Label(6, i + 1,recruitment.get(i).getProbationperiod()));
            	}else{
            		sheet.addCell(new Label(6, i + 1, "无"));
            	}
            	sheet.addCell(new Label(7, i + 1, recruitment.get(i).getPhone()+""));
            	if(recruitment.get(i).getTransactionsituation()!=null){
            		sheet.addCell(new Label(8, i + 1, recruitment.get(i).getTransactionsituation()+""));
            	}else{
            		sheet.addCell(new Label(8, i + 1, "无"));
            	}
            	if(recruitment.get(i).getBoccardnumber()!=null){
            		sheet.addCell(new Label(9, i + 1, recruitment.get(i).getBoccardnumber()+""));
            	}else{
            		sheet.addCell(new Label(9, i + 1, "无"));
            	}
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
	}
	/**
	 * 物品申请列表下载
	 * @param goodsNewList
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public static String downloadGoodsInfomationList(List<GoodsNew> goodsNewList, String startTime, String endTime) {
		String title[] = {"办公地址", "申请人", "采购人", "物品分类", "物品名称", "物品类型", "申请时间", "状态", "快递单号", "采购链接", "备注"};
		String name = "";
        //操作执行
        try {
        	  SimpleDateFormat sdf= new SimpleDateFormat("yyyyMMddHHmmss");
        	//t.xls为要新建的文件名
            name = "物品申请列表" + "-" + sdf.format(new Date()) + ".xls";
//            name = "goodsApplyList" + "-" + sdf1.format(new Date()) + ".xls";
             WritableWorkbook book = Workbook.createWorkbook(new File("/mydata/uploadtea/xls/" + name));
//             WritableWorkbook book= Workbook.createWorkbook(new File("G:/xuyafei/mydata/uploadoa/"+name));
            //生成名为“第一页”的工作表，参数0表示这是第一页
            WritableSheet sheet = book.createSheet("物品申请列表", 0);
            //设置列宽
            sheet.getSettings().setDefaultColumnWidth(20);
            //设置行高
            sheet.getSettings().setDefaultRowHeight(400);
            //设置字体
            WritableFont titleColor = new WritableFont(WritableFont.ARIAL, 12,WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,Colour.RED);
            WritableCellFormat titleFormat = new WritableCellFormat(titleColor);
            //设置背景色
            titleFormat.setBackground(Colour.LIGHT_GREEN);
            //添加表头
            for (int i = 0; i < title.length; i++) {    //title
                sheet.addCell(new Label(i, 0, title[i], titleFormat));
            }
            //写入内容
            for (int i = 0; i < goodsNewList.size(); i++){
            	sheet.addCell(new Label(0, i + 1, goodsNewList.get(i).getCenter_name()+""));
            	sheet.addCell(new Label(1, i + 1, goodsNewList.get(i).getSq_user()+""));
            	sheet.addCell(new Label(2, i + 1, goodsNewList.get(i).getPurchaser()+""));
            	sheet.addCell(new Label(3, i + 1, goodsNewList.get(i).getGoods_name()+""));
        		sheet.addCell(new Label(4, i + 1, goodsNewList.get(i).getItem_name()+""));
        		sheet.addCell(new Label(5, i + 1, goodsNewList.get(i).getType()+""));
        		sheet.addCell(new Label(6, i + 1, goodsNewList.get(i).getCreate_time()+""));
        		sheet.addCell(new Label(7, i + 1, goodsNewList.get(i).getApply_status()+""));
        		if(goodsNewList.get(i).getPurchasing_num()!=null){
        			sheet.addCell(new Label(8, i + 1, goodsNewList.get(i).getPurchasing_num()+""));
        		}else {
        			sheet.addCell(new Label(8, i + 1, "无"));
        		}
            	if(goodsNewList.get(i).getPurchasing_link()!=null){
            		sheet.addCell(new Label(9, i + 1, goodsNewList.get(i).getPurchasing_link()+""));
            	}else{
            		sheet.addCell(new Label(9, i + 1, "无"));
            	}
            	if(goodsNewList.get(i).getMsg()!=null){
            		sheet.addCell(new Label(10, i + 1, goodsNewList.get(i).getMsg()+""));
            	}else{
            		sheet.addCell(new Label(10, i + 1, "无"));
            	}
            }
            //写入数据
            book.write();
            //关闭文件
            book.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return name;
	}
    
}